import math

from odoo import api, fields, models, SUPERUSER_ID, _
from odoo import tools


class SaleOrderSearch(models.Model):
    _name = 's.sale.order.search'
    _description = 'Sale Order Search'
    _auto = False

    name = fields.Char(string='Name')
    special_order_type = fields.Selection([
        ('eq_only', 'EQ only'),
        ('free_cost', 'Free cost'),
        ('in_stock', 'In stock'),
        ('wb', 'WB'),
        ('prepare_materials', 'Prepare Materials'),
    ], string="Special Order Type")
    customer_code = fields.Char(string='Customer Code')
    end_po = fields.Char(string='E.PO')
    end_pn = fields.Char(string='E.PN')
    po = fields.Char(string='PO')
    pn = fields.Char(string='PN')
    pn_des = fields.Char('PN Desc')
    create_date = fields.Datetime(string='Create Date')
    commitment_date = fields.Datetime(string='Commitment Date')
    planned_receiving_date = fields.Datetime(string='Planned Receiving Date')
    product_id = fields.Many2one('product.product', string='Product')
    order_qty = fields.Integer(string='Order Qty')
    qty_delivered = fields.Integer(string='Qty Delivered')
    qty_undelivered = fields.Integer(string='Qty Undelivered')

    allocated_material_qty = fields.Integer(string='Allocated Material Qty', compute='_compute_allocated_material_qty')  # 在线数量
    scrap_qty = fields.Integer(string='Scrap Qty', compute='_compute_scrap_qty')  # 报废数量

    sb_workflow_state = fields.Selection([
        ('approval', 'Approval'),
        ('approve', 'Approve'),
        ('fail_to_approve', 'Refuse')
    ], string='Approve State')
    planned_qty = fields.Integer(string='Planned Qty')
    order_id = fields.Many2one('sale.order', string='Sale Order')
    sale_line_id = fields.Many2one('sale.order.line', string='Sale Line')
    user_id = fields.Many2one('res.users', string='Salesperson', related='order_id.user_id')
    remark = fields.Html(string='remark', related='order_id.note')
    partner_id = fields.Many2one('res.partner', string='Customer', related='order_id.partner_id')

    stock_in_qty = fields.Integer('Stock IN Qty', compute='_compute_scrap_qty')  # 入库数量
    stock_production_qty = fields.Integer('Stock Production Qty', compute='_compute_qty_available')  # 生产前数量

    qty_available = fields.Integer(string='Qty Avaliable', compute='_compute_qty_available')

    order_area = fields.Float('Order Area')
    remaining_planned_qty = fields.Integer('Remaining planned quantity')

    # def _compute_stock_in_qty(self):
    #     pre_production_location = self.env['stock.location'].search([('barcode', '=', 'WH-PREPRODUCTION')], limit=1)
    #     finished_location = self.env['stock.location'].search([('barcode', '=', 'WH-STOCK-FINISHED-PRODUCT')], limit=1)
    #     for rec in self:
    #         product_ids = rec.product_id.s_base_id.product_tmpl_ids.mapped('product_variant_ids')
    #         product_ids |= rec.product_id
    #
    #         lot_ids = rec.sale_line_id.s_mrp_production_ids.filtered(lambda x: x.state != 'cancel' and x.mo_type != 'C').mapped('finished_move_line_ids.lot_id')
    #         move_lines = self.env['stock.move.line'].search([
    #             ('state', '=', 'done'),
    #             ('location_id', '=', pre_production_location.id),
    #             ('location_dest_id', '=', finished_location.id),
    #             ('product_id', 'in', product_ids.ids),
    #             ('lot_id', 'in', lot_ids.ids)
    #         ])
    #         rec.stock_in_qty = sum(move_lines.mapped('quantity'))
    #         rec.stock_production_qty = sum(rec.product_id.stock_quant_ids.filtered(lambda x: x.location_id.barcode == 'WH-PREPRODUCTION' and x.lot_id in lot_ids).mapped('quantity'))

    def _compute_scrap_qty(self):
        # pre_production_location = self.env['stock.location'].search([('barcode', '=', 'WH-PREPRODUCTION')], limit=1)
        finished_location = self.env['stock.location'].search([('barcode', '=', 'WH-STOCK-FINISHED-PRODUCT')], limit=1)
        for rec in self:
            product_ids = rec.product_id.s_base_id.product_tmpl_ids.mapped('product_variant_ids')
            product_ids |= rec.product_id
            if not rec.planned_qty:
                rec.scrap_qty = 0
                rec.stock_in_qty = 0
            else:
                scrap_qty = 0
                stock_in_qty = 0
                stock_production_qty = 0
                valid_productions = rec.sale_line_id.s_mrp_production_ids.filtered(lambda x: x.state != 'cancel' and x.mo_type != 'C')
                # 多个销售单关联的生产单
                many_production_ids = self.env['mrp.production']
                # 销售单独有的生产单
                production_ids = self.env['mrp.production']
                for p in valid_productions:
                    if p.mapped('s_sale_order_line_ids').filtered(lambda x: x.state != 'cancel') - rec.sale_line_id:
                        many_production_ids |= p
                    else:
                        production_ids |= p

                if many_production_ids:
                    all_sale_line_ids = many_production_ids \
                        .mapped('s_sale_order_line_ids') \
                        .filtered(lambda x: x.product_uom_qty > 0)
                    total_sale_qty = sum(all_sale_line_ids.mapped('product_uom_qty'))
                    if total_sale_qty:
                        ratio = rec.sale_line_id.product_uom_qty / total_sale_qty
                        # 报废
                        total_sale_qty = sum(all_sale_line_ids.mapped('product_uom_qty'))
                        many_scrap_total = sum(many_production_ids.mapped('qty_reject'))
                        many_scrap_qty = math.ceil(many_scrap_total * ratio)
                        scrap_qty += many_scrap_qty

                        m_lot_ids = many_production_ids.filtered(lambda x: x.state != 'cancel' and x.mo_type != 'C').mapped('finished_move_line_ids.lot_id')

                        # 入库
                        m_move_lines = self.env['stock.move.line'].search([
                            ('state', '=', 'done'),
                            ('location_id.usage', 'not in', ['supplier', 'customer']),
                            ('location_dest_id', '=', finished_location.id),
                            ('product_id', 'in', product_ids.ids),
                            ('lot_id', 'in', m_lot_ids.ids)
                        ])
                        stock_in_qty += math.ceil(sum(m_move_lines.mapped('quantity')) * ratio)

                rec.scrap_qty = sum(production_ids.mapped('qty_reject')) + scrap_qty

                lot_ids = production_ids.filtered(lambda x: x.state != 'cancel' and x.mo_type != 'C').mapped('finished_move_line_ids.lot_id')
                move_lines = self.env['stock.move.line'].search([
                    ('state', '=', 'done'),
                    ('location_id.usage', 'not in', ['supplier', 'customer']),
                    ('location_dest_id', '=', finished_location.id),
                    ('product_id', 'in', product_ids.ids),
                    ('lot_id', 'in', lot_ids.ids)
                ])
                rec.stock_in_qty = min(sum(move_lines.mapped('quantity')) + stock_in_qty, rec.planned_qty - rec.scrap_qty)

    def _compute_allocated_material_qty(self):
        for rec in self:
            if not rec.planned_qty:
                rec.allocated_material_qty = 0
            else:

                valid_productions = rec.sale_line_id.s_mrp_production_ids.filtered(lambda x: x.state not in ('cancel', 'done') and x.mo_type != 'C')
                # 多个销售单关联的生产单
                many_production_ids = self.env['mrp.production']
                # 销售单独有的生产单
                production_ids = self.env['mrp.production']
                allocated_material_qty = 0
                for p in valid_productions:
                    if p.mapped('s_sale_order_line_ids').filtered(lambda x: x.state != 'cancel') - rec.sale_line_id:
                        many_production_ids |= p
                    else:
                        production_ids |= p

                all_sale_line_ids = many_production_ids \
                    .mapped('s_sale_order_line_ids') \
                    .filtered(lambda x: x.product_uom_qty > 0)

                # 计算总的在线数量
                total_planned_qty = 0.0
                for many_production in many_production_ids:
                    all_children_production = many_production._get_all_child().filtered(lambda x: x.state != 'cancel')
                    if all_children_production and all_children_production.filtered(lambda x: x.state != 'done'):
                        total_planned_qty += many_production.product_qty
                    else:
                        total_planned_qty += sum(many_production.mapped('workorder_ids.qty_wip'))

                if total_planned_qty:
                    total_sale_qty = sum(all_sale_line_ids.mapped('product_uom_qty'))
                    allocated_material_qty += math.ceil(total_planned_qty * (rec.sale_line_id.product_uom_qty / total_sale_qty))

                for production in production_ids:
                    all_children_production_ids = production._get_all_child().filtered(lambda x: x.state != 'cancel')
                    if all_children_production_ids and all_children_production_ids.filtered(lambda x: x.state != 'done'):
                        allocated_material_qty += production.product_qty
                    else:
                        allocated_material_qty += sum(production.mapped('workorder_ids.qty_wip'))

                rec.allocated_material_qty = allocated_material_qty

    def _compute_qty_available(self):
        for order in self:
            product_ids = order.product_id.s_base_id.product_tmpl_ids.mapped('product_variant_ids')
            product_ids |= order.product_id

            # 库存在手
            order.qty_available = sum(product_ids.mapped('s_actual_quantity'))
            # 生产前在手
            lot_ids = order.sale_line_id.s_mrp_production_ids.filtered(lambda x: x.state != 'cancel' and x.mo_type != 'C').mapped('finished_move_line_ids.lot_id')
            order.stock_production_qty = sum(
                order.product_id.stock_quant_ids.filtered(lambda x: x.location_id.barcode == 'WH-PREPRODUCTION' and x.lot_id in lot_ids).mapped('quantity'))

    @property
    def _table_query(self):
        sql = """ 
            SELECT
                sol.id AS id,
                sol.id sale_line_id,
                so.NAME,
                so.special_order_type,
                rp.code AS customer_code,
                so.end_po,
                so.end_pn,
                so.po,
                so.pn,
                cic.customer_item_desc pn_des,
                so.create_date,
                so.commitment_date,
                so.planned_receiving_date,
                sol.product_id,
                case when puva3.uom_value = 0 then 0
                else sol.product_uom_qty * puva.uom_value * puva2.uom_value / puva3.uom_value / 1000000 end order_area,
                sol.s_remaining_planned_qty remaining_planned_qty,
                sol.product_uom_qty order_qty,
                COALESCE ( sol.qty_delivered, 0 )  AS qty_delivered,
                COALESCE ( sol.s_planned_qty, 0 )  AS planned_qty ,
                sol.product_uom_qty - sol.qty_delivered AS qty_undelivered,
                so.sb_workflow_state,
                sol.s_planned_qty,
                so.ID as order_id
            FROM
                sale_order so
                left join sale_order_line sol on so.id = sol.order_id
                LEFT JOIN res_partner rp ON so.partner_id = rp.ID
                LEFT JOIN product_product pp ON sol.product_id = pp.ID
                LEFT JOIN product_template pt ON pp.product_tmpl_id = pt.ID
                left join product_uom_value puva on puva.product_id = pt.id and puva.uom_id in (select id from uom_uom where uom_uom.code = 'SETL')
                left join product_uom_value puva2 on puva2.product_id = pt.id and puva2.uom_id in (select id from uom_uom where uom_uom.code = 'SETW')
                left join product_uom_value puva3 on puva3.product_id = pt.id and puva3.uom_id in (select id from uom_uom where uom_uom.code = 'SET')
                left join customer_item_code cic on sol.s_customer_item_code = cic.id
            WHERE
                so.state != 'cancel'
                AND pt.detailed_type != 'service'
                AND so.status != 'cancelled'

        """
        return sql
